import mysql.connector
from flask import current_app, g
from flask.cli import with_appcontext
import click

def get_db():
    """
    获取数据库连接
    """
    if 'db' not in g:
        g.db = mysql.connector.connect(
            user='root',
            password='root',
            host='127.0.0.1',
            port=3306,
            database='vue3_blog'
        )

    return g.db


def close_db(e=None):
    """
    关闭数据库连接
    """
    db = g.pop('db', None)

    if db is not None:
        db.close()

def init_app(app):
    """
    将数据库操作函数注册到 Flask 应用程序中
    """
    app.teardown_appcontext(close_db)
    create_tables()



def create_tables():
    """
    创建数据库表
    """
    db = get_db()
    cursor = db.cursor()
    # 检查表users中是否存在nickname为"pro"的数据

    query = "SELECT * FROM users WHERE nickname = %s"
    cursor.execute(query, ("passlink_program",))
    result = cursor.fetchone()
    # 如果结果为空，则插入新数据
    if result is None:
        insert_query = "INSERT INTO users (user_id,nickname,password) VALUES (%s,%s,%s)"
        cursor.execute(insert_query, ("ddcser4fcdxfwe3rd","passlink_program","scrypt:32768:8:1$NWyr4OqtVBZeAEXp$5a0716195f30f2ec0ea03552ffe778969aa13154d9d2811e414bc1f586e0736cf057bea8e929544a8d516620293868f490bdcbdb4dd8245af27e0446a8307f54"))
        db.commit()
        print("创建成功")
    else:
        print("数据已存在")

    # 检查 process_list、process_program 和 programs 表是否存在
    cursor.execute("SHOW TABLES")
    tables = [table[0] for table in cursor.fetchall()]
    if 'process_list'  in tables and 'process_program'  in tables and 'programs'  in tables:
        db.commit()
        cursor.close()
        db.close()
        return
    # 创建 process_list 表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS `process_list` (
            `process_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL COMMENT 'id',
            `create_time` datetime NOT NULL COMMENT '创建时间',
            `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL COMMENT '标题',
            `introduce` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL COMMENT '介绍',
            `img1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL COMMENT '图1',
            `img2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL COMMENT '图2',
            `img3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL COMMENT '图3',
            `img4` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL COMMENT '图4',
            `img5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL COMMENT '图5',
            PRIMARY KEY (`process_id`) USING BTREE
        ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_as_ci ROW_FORMAT = Dynamic
    """)

    # 创建 process_program 表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS `process_program` (
            `id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
            `process_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL COMMENT '流程id',
            `program_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL COMMENT '项目id',
            PRIMARY KEY (`id`) USING BTREE
        ) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_as_ci ROW_FORMAT = Dynamic
    """)
    # 创建 programs 表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS `programs` (
            `program_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL COMMENT 'id',
            `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
            `title` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL COMMENT '标题',
            `introduction` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL COMMENT '简介',
            `main_img` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL COMMENT '大图',
            PRIMARY KEY (`program_id`) USING BTREE
        ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_as_ci ROW_FORMAT = Dynamic ;
    """)

    db.commit()
    cursor.close()
    db.close()